{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cf7c9512",
   "metadata": {},
   "source": [
    "# 阶段五模块四作业"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "b3664be7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68c0d820",
   "metadata": {},
   "source": [
    "## 要求1-3：数据加载"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "aa9be6c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5'19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>7042</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>5755</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>5688</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别 男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0     1  男    4'13   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1     1  男    4'16   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2     1  男    4'09   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3     1  男    4'21   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4     1  男    3'44   7.52  210.0    13    9  3538  171.0  54.7    0\n",
       "..   .. ..     ...    ...    ...   ...  ...   ...    ...   ...  ...\n",
       "472  17  男    4'23   8.27  208.0    10    0  4647  176.0  69.5    0\n",
       "473  17  男    5'19   9.55  210.0    15    6  7042  177.0  76.0    0\n",
       "474  17  男    3'25   7.50  252.0    13   13  5755  181.0  65.0    0\n",
       "475  17  男    4'39   7.81  208.0    14   11  5688  172.0  51.7    0\n",
       "476  17  男       0   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "\n",
       "[477 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16</td>\n",
       "      <td>48</td>\n",
       "      <td>3775</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "      <td>3683</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>3331</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>3701</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8</td>\n",
       "      <td>34</td>\n",
       "      <td>3592</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24</td>\n",
       "      <td>41</td>\n",
       "      <td>2255</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13</td>\n",
       "      <td>36</td>\n",
       "      <td>2937</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15</td>\n",
       "      <td>35</td>\n",
       "      <td>2592</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10</td>\n",
       "      <td>41</td>\n",
       "      <td>1829</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10</td>\n",
       "      <td>46</td>\n",
       "      <td>2962</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈  女仰卧  女肺活量     身高    体重  BMI\n",
       "0     1  女    3.22   9.32  185.0    16   48  3775  163.0  51.3    0\n",
       "1     1  女    4.59  11.44  148.0     9   29  3683  163.0  66.6    0\n",
       "2     1  女    3.46  13.40  150.0     7   40  3331  157.0  60.0    0\n",
       "3     1  女    3.39   9.52  172.0    21   46  3701  160.0  50.7    0\n",
       "4     1  女    3.43   9.79  145.0     8   34  3592  167.0  63.9    0\n",
       "..   .. ..     ...    ...    ...   ...  ...   ...    ...   ...  ...\n",
       "588  17  女    3.51   9.60  150.0    24   41  2255  158.0  49.0    0\n",
       "589  17  女    4.00  10.18  150.0    13   36  2937  161.0  55.7    0\n",
       "590  17  女    3.45  10.18  152.0    15   35  2592  165.0  48.6    0\n",
       "591  17  女    4.01   9.67  165.0    10   41  1829  154.0  43.6    0\n",
       "592  17  女    4.48   9.09  180.0    10   46  2962  162.0  55.3    0\n",
       "\n",
       "[593 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">男肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男体前屈</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女跳远</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男引体</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>100</td>\n",
       "      <td>3'24\"</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>95</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>3'35\"</td>\n",
       "      <td>95</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>3'40\"</td>\n",
       "      <td>90</td>\n",
       "      <td>3'36\"</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>85</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3'47\"</td>\n",
       "      <td>85</td>\n",
       "      <td>3'43\"</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>80</td>\n",
       "      <td>3'50\"</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3680</td>\n",
       "      <td>78</td>\n",
       "      <td>2650</td>\n",
       "      <td>78</td>\n",
       "      <td>7.7</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>78</td>\n",
       "      <td>13.6</td>\n",
       "      <td>78</td>\n",
       "      <td>...</td>\n",
       "      <td>175</td>\n",
       "      <td>78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>4'00\"</td>\n",
       "      <td>78</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3560</td>\n",
       "      <td>76</td>\n",
       "      <td>2550</td>\n",
       "      <td>76</td>\n",
       "      <td>7.9</td>\n",
       "      <td>76</td>\n",
       "      <td>9.0</td>\n",
       "      <td>76</td>\n",
       "      <td>12.2</td>\n",
       "      <td>76</td>\n",
       "      <td>...</td>\n",
       "      <td>172</td>\n",
       "      <td>76</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76</td>\n",
       "      <td>39</td>\n",
       "      <td>76</td>\n",
       "      <td>4'05\"</td>\n",
       "      <td>76</td>\n",
       "      <td>4'00\"</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3440</td>\n",
       "      <td>74</td>\n",
       "      <td>2450</td>\n",
       "      <td>74</td>\n",
       "      <td>8.1</td>\n",
       "      <td>74</td>\n",
       "      <td>9.2</td>\n",
       "      <td>74</td>\n",
       "      <td>10.8</td>\n",
       "      <td>74</td>\n",
       "      <td>...</td>\n",
       "      <td>169</td>\n",
       "      <td>74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>37</td>\n",
       "      <td>74</td>\n",
       "      <td>4'10\"</td>\n",
       "      <td>74</td>\n",
       "      <td>4'05\"</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3320</td>\n",
       "      <td>72</td>\n",
       "      <td>2350</td>\n",
       "      <td>72</td>\n",
       "      <td>8.3</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>...</td>\n",
       "      <td>166</td>\n",
       "      <td>72</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>72</td>\n",
       "      <td>4'15\"</td>\n",
       "      <td>72</td>\n",
       "      <td>4'10\"</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3200</td>\n",
       "      <td>70</td>\n",
       "      <td>2250</td>\n",
       "      <td>70</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>9.6</td>\n",
       "      <td>70</td>\n",
       "      <td>8.0</td>\n",
       "      <td>70</td>\n",
       "      <td>...</td>\n",
       "      <td>163</td>\n",
       "      <td>70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>33</td>\n",
       "      <td>70</td>\n",
       "      <td>4'20\"</td>\n",
       "      <td>70</td>\n",
       "      <td>4'15\"</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3080</td>\n",
       "      <td>68</td>\n",
       "      <td>2150</td>\n",
       "      <td>68</td>\n",
       "      <td>8.7</td>\n",
       "      <td>68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>68</td>\n",
       "      <td>6.6</td>\n",
       "      <td>68</td>\n",
       "      <td>...</td>\n",
       "      <td>160</td>\n",
       "      <td>68</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68</td>\n",
       "      <td>31</td>\n",
       "      <td>68</td>\n",
       "      <td>4'25\"</td>\n",
       "      <td>68</td>\n",
       "      <td>4'20\"</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2960</td>\n",
       "      <td>66</td>\n",
       "      <td>2050</td>\n",
       "      <td>66</td>\n",
       "      <td>8.9</td>\n",
       "      <td>66</td>\n",
       "      <td>10.0</td>\n",
       "      <td>66</td>\n",
       "      <td>5.2</td>\n",
       "      <td>66</td>\n",
       "      <td>...</td>\n",
       "      <td>157</td>\n",
       "      <td>66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>66</td>\n",
       "      <td>4'30\"</td>\n",
       "      <td>66</td>\n",
       "      <td>4'25\"</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2840</td>\n",
       "      <td>64</td>\n",
       "      <td>1950</td>\n",
       "      <td>64</td>\n",
       "      <td>9.1</td>\n",
       "      <td>64</td>\n",
       "      <td>10.2</td>\n",
       "      <td>64</td>\n",
       "      <td>3.8</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>154</td>\n",
       "      <td>64</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64</td>\n",
       "      <td>27</td>\n",
       "      <td>64</td>\n",
       "      <td>4'35\"</td>\n",
       "      <td>64</td>\n",
       "      <td>4'30\"</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2720</td>\n",
       "      <td>62</td>\n",
       "      <td>1850</td>\n",
       "      <td>62</td>\n",
       "      <td>9.3</td>\n",
       "      <td>62</td>\n",
       "      <td>10.4</td>\n",
       "      <td>62</td>\n",
       "      <td>2.4</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>151</td>\n",
       "      <td>62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>25</td>\n",
       "      <td>62</td>\n",
       "      <td>4'40\"</td>\n",
       "      <td>62</td>\n",
       "      <td>4'35\"</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2600</td>\n",
       "      <td>60</td>\n",
       "      <td>1750</td>\n",
       "      <td>60</td>\n",
       "      <td>9.5</td>\n",
       "      <td>60</td>\n",
       "      <td>10.6</td>\n",
       "      <td>60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>148</td>\n",
       "      <td>60</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60</td>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>4'45\"</td>\n",
       "      <td>60</td>\n",
       "      <td>4'40\"</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2470</td>\n",
       "      <td>50</td>\n",
       "      <td>1710</td>\n",
       "      <td>50</td>\n",
       "      <td>9.7</td>\n",
       "      <td>50</td>\n",
       "      <td>10.8</td>\n",
       "      <td>50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>143</td>\n",
       "      <td>50</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "      <td>21</td>\n",
       "      <td>50</td>\n",
       "      <td>5'05\"</td>\n",
       "      <td>50</td>\n",
       "      <td>4'50\"</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2340</td>\n",
       "      <td>40</td>\n",
       "      <td>1670</td>\n",
       "      <td>40</td>\n",
       "      <td>9.9</td>\n",
       "      <td>40</td>\n",
       "      <td>11.0</td>\n",
       "      <td>40</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>40</td>\n",
       "      <td>...</td>\n",
       "      <td>138</td>\n",
       "      <td>40</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>40</td>\n",
       "      <td>5'25\"</td>\n",
       "      <td>40</td>\n",
       "      <td>5'00\"</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2210</td>\n",
       "      <td>30</td>\n",
       "      <td>1630</td>\n",
       "      <td>30</td>\n",
       "      <td>10.1</td>\n",
       "      <td>30</td>\n",
       "      <td>11.2</td>\n",
       "      <td>30</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>133</td>\n",
       "      <td>30</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "      <td>5'45\"</td>\n",
       "      <td>30</td>\n",
       "      <td>5'10\"</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2080</td>\n",
       "      <td>20</td>\n",
       "      <td>1590</td>\n",
       "      <td>20</td>\n",
       "      <td>10.3</td>\n",
       "      <td>20</td>\n",
       "      <td>11.4</td>\n",
       "      <td>20</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>128</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>6'05\"</td>\n",
       "      <td>20</td>\n",
       "      <td>5'20\"</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1950</td>\n",
       "      <td>10</td>\n",
       "      <td>1550</td>\n",
       "      <td>10</td>\n",
       "      <td>10.5</td>\n",
       "      <td>10</td>\n",
       "      <td>11.6</td>\n",
       "      <td>10</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>123</td>\n",
       "      <td>10</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>6'25\"</td>\n",
       "      <td>10</td>\n",
       "      <td>5'30\"</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    男肺活量       女肺活量      男50米跑      女50米跑       男体前屈       ...  女跳远       \\\n",
       "      成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩   分数  ...   成绩   分数   \n",
       "0   4540  100  3150  100   7.1  100   7.8  100  23.6  100  ...  204  100   \n",
       "1   4420   95  3100   95   7.2   95   7.9   95  21.5   95  ...  198   95   \n",
       "2   4300   90  3050   90   7.3   90   8.0   90  19.4   90  ...  192   90   \n",
       "3   4050   85  2900   85   7.4   85   8.3   85  17.2   85  ...  185   85   \n",
       "4   3800   80  2750   80   7.5   80   8.6   80  15.0   80  ...  178   80   \n",
       "5   3680   78  2650   78   7.7   78   8.8   78  13.6   78  ...  175   78   \n",
       "6   3560   76  2550   76   7.9   76   9.0   76  12.2   76  ...  172   76   \n",
       "7   3440   74  2450   74   8.1   74   9.2   74  10.8   74  ...  169   74   \n",
       "8   3320   72  2350   72   8.3   72   9.4   72   9.4   72  ...  166   72   \n",
       "9   3200   70  2250   70   8.5   70   9.6   70   8.0   70  ...  163   70   \n",
       "10  3080   68  2150   68   8.7   68   9.8   68   6.6   68  ...  160   68   \n",
       "11  2960   66  2050   66   8.9   66  10.0   66   5.2   66  ...  157   66   \n",
       "12  2840   64  1950   64   9.1   64  10.2   64   3.8   64  ...  154   64   \n",
       "13  2720   62  1850   62   9.3   62  10.4   62   2.4   62  ...  151   62   \n",
       "14  2600   60  1750   60   9.5   60  10.6   60   1.0   60  ...  148   60   \n",
       "15  2470   50  1710   50   9.7   50  10.8   50   0.0   50  ...  143   50   \n",
       "16  2340   40  1670   40   9.9   40  11.0   40  -1.0   40  ...  138   40   \n",
       "17  2210   30  1630   30  10.1   30  11.2   30  -2.0   30  ...  133   30   \n",
       "18  2080   20  1590   20  10.3   20  11.4   20  -3.0   20  ...  128   20   \n",
       "19  1950   10  1550   10  10.5   10  11.6   10  -4.0   10  ...  123   10   \n",
       "\n",
       "     男引体      女仰卧      男1000米跑      女800米跑       \n",
       "      成绩   分数  成绩   分数      成绩   分数     成绩   分数  \n",
       "0   16.0  100  53  100   3'30\"  100  3'24\"  100  \n",
       "1   15.0   95  51   95   3'35\"   95  3'30\"   95  \n",
       "2   14.0   90  49   90   3'40\"   90  3'36\"   90  \n",
       "3   13.0   85  46   85   3'47\"   85  3'43\"   85  \n",
       "4   12.0   80  43   80   3'55\"   80  3'50\"   80  \n",
       "5    NaN   78  41   78   4'00\"   78  3'55\"   78  \n",
       "6   11.0   76  39   76   4'05\"   76  4'00\"   76  \n",
       "7    NaN   74  37   74   4'10\"   74  4'05\"   74  \n",
       "8   10.0   72  35   72   4'15\"   72  4'10\"   72  \n",
       "9    NaN   70  33   70   4'20\"   70  4'15\"   70  \n",
       "10   9.0   68  31   68   4'25\"   68  4'20\"   68  \n",
       "11   NaN   66  29   66   4'30\"   66  4'25\"   66  \n",
       "12   8.0   64  27   64   4'35\"   64  4'30\"   64  \n",
       "13   NaN   62  25   62   4'40\"   62  4'35\"   62  \n",
       "14   7.0   60  23   60   4'45\"   60  4'40\"   60  \n",
       "15   6.0   50  21   50   5'05\"   50  4'50\"   50  \n",
       "16   5.0   40  19   40   5'25\"   40  5'00\"   40  \n",
       "17   4.0   30  17   30   5'45\"   30  5'10\"   30  \n",
       "18   3.0   20  15   20   6'05\"   20  5'20\"   20  \n",
       "19   2.0   10  13   10   6'25\"   10  5'30\"   10  \n",
       "\n",
       "[20 rows x 24 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DScoreBoy = pd.read_excel('./18级高一体测成绩汇总.xls',\n",
    "              sheet_name= 0,\n",
    "              header=0\n",
    "              )\n",
    "DScoreGirl = pd.read_excel('./18级高一体测成绩汇总.xls',\n",
    "              sheet_name= 1,\n",
    "              header=0\n",
    "              )\n",
    "DScoreStd = pd.read_excel('./体测成绩评分表.xls',\n",
    "              sheet_name= 0,\n",
    "              header=(0,1)\n",
    "              )\n",
    "display(DScoreBoy,DScoreGirl,DScoreStd)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93cb281d",
   "metadata": {},
   "source": [
    "空数据用字符0填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "e6b34adf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">男肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男体前屈</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女跳远</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男引体</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>100</td>\n",
       "      <td>3'24\"</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>95</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>3'35\"</td>\n",
       "      <td>95</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>3'40\"</td>\n",
       "      <td>90</td>\n",
       "      <td>3'36\"</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>85</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3'47\"</td>\n",
       "      <td>85</td>\n",
       "      <td>3'43\"</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>80</td>\n",
       "      <td>3'50\"</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3680</td>\n",
       "      <td>78</td>\n",
       "      <td>2650</td>\n",
       "      <td>78</td>\n",
       "      <td>7.7</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>78</td>\n",
       "      <td>13.6</td>\n",
       "      <td>78</td>\n",
       "      <td>...</td>\n",
       "      <td>175</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>78</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>4'00\"</td>\n",
       "      <td>78</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3560</td>\n",
       "      <td>76</td>\n",
       "      <td>2550</td>\n",
       "      <td>76</td>\n",
       "      <td>7.9</td>\n",
       "      <td>76</td>\n",
       "      <td>9.0</td>\n",
       "      <td>76</td>\n",
       "      <td>12.2</td>\n",
       "      <td>76</td>\n",
       "      <td>...</td>\n",
       "      <td>172</td>\n",
       "      <td>76</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76</td>\n",
       "      <td>39</td>\n",
       "      <td>76</td>\n",
       "      <td>4'05\"</td>\n",
       "      <td>76</td>\n",
       "      <td>4'00\"</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3440</td>\n",
       "      <td>74</td>\n",
       "      <td>2450</td>\n",
       "      <td>74</td>\n",
       "      <td>8.1</td>\n",
       "      <td>74</td>\n",
       "      <td>9.2</td>\n",
       "      <td>74</td>\n",
       "      <td>10.8</td>\n",
       "      <td>74</td>\n",
       "      <td>...</td>\n",
       "      <td>169</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>37</td>\n",
       "      <td>74</td>\n",
       "      <td>4'10\"</td>\n",
       "      <td>74</td>\n",
       "      <td>4'05\"</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3320</td>\n",
       "      <td>72</td>\n",
       "      <td>2350</td>\n",
       "      <td>72</td>\n",
       "      <td>8.3</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>...</td>\n",
       "      <td>166</td>\n",
       "      <td>72</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>72</td>\n",
       "      <td>4'15\"</td>\n",
       "      <td>72</td>\n",
       "      <td>4'10\"</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3200</td>\n",
       "      <td>70</td>\n",
       "      <td>2250</td>\n",
       "      <td>70</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>9.6</td>\n",
       "      <td>70</td>\n",
       "      <td>8.0</td>\n",
       "      <td>70</td>\n",
       "      <td>...</td>\n",
       "      <td>163</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>70</td>\n",
       "      <td>33</td>\n",
       "      <td>70</td>\n",
       "      <td>4'20\"</td>\n",
       "      <td>70</td>\n",
       "      <td>4'15\"</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3080</td>\n",
       "      <td>68</td>\n",
       "      <td>2150</td>\n",
       "      <td>68</td>\n",
       "      <td>8.7</td>\n",
       "      <td>68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>68</td>\n",
       "      <td>6.6</td>\n",
       "      <td>68</td>\n",
       "      <td>...</td>\n",
       "      <td>160</td>\n",
       "      <td>68</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68</td>\n",
       "      <td>31</td>\n",
       "      <td>68</td>\n",
       "      <td>4'25\"</td>\n",
       "      <td>68</td>\n",
       "      <td>4'20\"</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2960</td>\n",
       "      <td>66</td>\n",
       "      <td>2050</td>\n",
       "      <td>66</td>\n",
       "      <td>8.9</td>\n",
       "      <td>66</td>\n",
       "      <td>10.0</td>\n",
       "      <td>66</td>\n",
       "      <td>5.2</td>\n",
       "      <td>66</td>\n",
       "      <td>...</td>\n",
       "      <td>157</td>\n",
       "      <td>66</td>\n",
       "      <td>0</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>66</td>\n",
       "      <td>4'30\"</td>\n",
       "      <td>66</td>\n",
       "      <td>4'25\"</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2840</td>\n",
       "      <td>64</td>\n",
       "      <td>1950</td>\n",
       "      <td>64</td>\n",
       "      <td>9.1</td>\n",
       "      <td>64</td>\n",
       "      <td>10.2</td>\n",
       "      <td>64</td>\n",
       "      <td>3.8</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>154</td>\n",
       "      <td>64</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64</td>\n",
       "      <td>27</td>\n",
       "      <td>64</td>\n",
       "      <td>4'35\"</td>\n",
       "      <td>64</td>\n",
       "      <td>4'30\"</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2720</td>\n",
       "      <td>62</td>\n",
       "      <td>1850</td>\n",
       "      <td>62</td>\n",
       "      <td>9.3</td>\n",
       "      <td>62</td>\n",
       "      <td>10.4</td>\n",
       "      <td>62</td>\n",
       "      <td>2.4</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>151</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "      <td>25</td>\n",
       "      <td>62</td>\n",
       "      <td>4'40\"</td>\n",
       "      <td>62</td>\n",
       "      <td>4'35\"</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2600</td>\n",
       "      <td>60</td>\n",
       "      <td>1750</td>\n",
       "      <td>60</td>\n",
       "      <td>9.5</td>\n",
       "      <td>60</td>\n",
       "      <td>10.6</td>\n",
       "      <td>60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>148</td>\n",
       "      <td>60</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60</td>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>4'45\"</td>\n",
       "      <td>60</td>\n",
       "      <td>4'40\"</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2470</td>\n",
       "      <td>50</td>\n",
       "      <td>1710</td>\n",
       "      <td>50</td>\n",
       "      <td>9.7</td>\n",
       "      <td>50</td>\n",
       "      <td>10.8</td>\n",
       "      <td>50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>143</td>\n",
       "      <td>50</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "      <td>21</td>\n",
       "      <td>50</td>\n",
       "      <td>5'05\"</td>\n",
       "      <td>50</td>\n",
       "      <td>4'50\"</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2340</td>\n",
       "      <td>40</td>\n",
       "      <td>1670</td>\n",
       "      <td>40</td>\n",
       "      <td>9.9</td>\n",
       "      <td>40</td>\n",
       "      <td>11.0</td>\n",
       "      <td>40</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>40</td>\n",
       "      <td>...</td>\n",
       "      <td>138</td>\n",
       "      <td>40</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>40</td>\n",
       "      <td>5'25\"</td>\n",
       "      <td>40</td>\n",
       "      <td>5'00\"</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2210</td>\n",
       "      <td>30</td>\n",
       "      <td>1630</td>\n",
       "      <td>30</td>\n",
       "      <td>10.1</td>\n",
       "      <td>30</td>\n",
       "      <td>11.2</td>\n",
       "      <td>30</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>133</td>\n",
       "      <td>30</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "      <td>5'45\"</td>\n",
       "      <td>30</td>\n",
       "      <td>5'10\"</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2080</td>\n",
       "      <td>20</td>\n",
       "      <td>1590</td>\n",
       "      <td>20</td>\n",
       "      <td>10.3</td>\n",
       "      <td>20</td>\n",
       "      <td>11.4</td>\n",
       "      <td>20</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>128</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>6'05\"</td>\n",
       "      <td>20</td>\n",
       "      <td>5'20\"</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1950</td>\n",
       "      <td>10</td>\n",
       "      <td>1550</td>\n",
       "      <td>10</td>\n",
       "      <td>10.5</td>\n",
       "      <td>10</td>\n",
       "      <td>11.6</td>\n",
       "      <td>10</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>123</td>\n",
       "      <td>10</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>6'25\"</td>\n",
       "      <td>10</td>\n",
       "      <td>5'30\"</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    男肺活量       女肺活量      男50米跑      女50米跑       男体前屈       ...  女跳远       \\\n",
       "      成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩   分数  ...   成绩   分数   \n",
       "0   4540  100  3150  100   7.1  100   7.8  100  23.6  100  ...  204  100   \n",
       "1   4420   95  3100   95   7.2   95   7.9   95  21.5   95  ...  198   95   \n",
       "2   4300   90  3050   90   7.3   90   8.0   90  19.4   90  ...  192   90   \n",
       "3   4050   85  2900   85   7.4   85   8.3   85  17.2   85  ...  185   85   \n",
       "4   3800   80  2750   80   7.5   80   8.6   80  15.0   80  ...  178   80   \n",
       "5   3680   78  2650   78   7.7   78   8.8   78  13.6   78  ...  175   78   \n",
       "6   3560   76  2550   76   7.9   76   9.0   76  12.2   76  ...  172   76   \n",
       "7   3440   74  2450   74   8.1   74   9.2   74  10.8   74  ...  169   74   \n",
       "8   3320   72  2350   72   8.3   72   9.4   72   9.4   72  ...  166   72   \n",
       "9   3200   70  2250   70   8.5   70   9.6   70   8.0   70  ...  163   70   \n",
       "10  3080   68  2150   68   8.7   68   9.8   68   6.6   68  ...  160   68   \n",
       "11  2960   66  2050   66   8.9   66  10.0   66   5.2   66  ...  157   66   \n",
       "12  2840   64  1950   64   9.1   64  10.2   64   3.8   64  ...  154   64   \n",
       "13  2720   62  1850   62   9.3   62  10.4   62   2.4   62  ...  151   62   \n",
       "14  2600   60  1750   60   9.5   60  10.6   60   1.0   60  ...  148   60   \n",
       "15  2470   50  1710   50   9.7   50  10.8   50   0.0   50  ...  143   50   \n",
       "16  2340   40  1670   40   9.9   40  11.0   40  -1.0   40  ...  138   40   \n",
       "17  2210   30  1630   30  10.1   30  11.2   30  -2.0   30  ...  133   30   \n",
       "18  2080   20  1590   20  10.3   20  11.4   20  -3.0   20  ...  128   20   \n",
       "19  1950   10  1550   10  10.5   10  11.6   10  -4.0   10  ...  123   10   \n",
       "\n",
       "     男引体      女仰卧      男1000米跑      女800米跑       \n",
       "      成绩   分数  成绩   分数      成绩   分数     成绩   分数  \n",
       "0   16.0  100  53  100   3'30\"  100  3'24\"  100  \n",
       "1   15.0   95  51   95   3'35\"   95  3'30\"   95  \n",
       "2   14.0   90  49   90   3'40\"   90  3'36\"   90  \n",
       "3   13.0   85  46   85   3'47\"   85  3'43\"   85  \n",
       "4   12.0   80  43   80   3'55\"   80  3'50\"   80  \n",
       "5      0   78  41   78   4'00\"   78  3'55\"   78  \n",
       "6   11.0   76  39   76   4'05\"   76  4'00\"   76  \n",
       "7      0   74  37   74   4'10\"   74  4'05\"   74  \n",
       "8   10.0   72  35   72   4'15\"   72  4'10\"   72  \n",
       "9      0   70  33   70   4'20\"   70  4'15\"   70  \n",
       "10   9.0   68  31   68   4'25\"   68  4'20\"   68  \n",
       "11     0   66  29   66   4'30\"   66  4'25\"   66  \n",
       "12   8.0   64  27   64   4'35\"   64  4'30\"   64  \n",
       "13     0   62  25   62   4'40\"   62  4'35\"   62  \n",
       "14   7.0   60  23   60   4'45\"   60  4'40\"   60  \n",
       "15   6.0   50  21   50   5'05\"   50  4'50\"   50  \n",
       "16   5.0   40  19   40   5'25\"   40  5'00\"   40  \n",
       "17   4.0   30  17   30   5'45\"   30  5'10\"   30  \n",
       "18   3.0   20  15   20   6'05\"   20  5'20\"   20  \n",
       "19   2.0   10  13   10   6'25\"   10  5'30\"   10  \n",
       "\n",
       "[20 rows x 24 columns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DScoreBoy.fillna(value='0')\n",
    "DScoreGirl.fillna(value='0')\n",
    "DScoreStd.fillna(value='0')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "372852b2",
   "metadata": {},
   "source": [
    "## 要求4：数据类型转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "4a4f9cbf",
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "df =pd.Series(data=DScoreBoy['男1000米跑'].astype('str'))\n",
    "x = 0\n",
    "pfloat = 0.0\n",
    "for i in range(0,len(df.index)):\n",
    "    if '\\''  in df[i]:\n",
    "        x= int(df[i][0])\n",
    "        if len(df[i]) ==4:\n",
    "            pfloat = x +int(df[i][2:4])/60\n",
    "        elif len(df[i]) ==3:\n",
    "            pfloat = x +int(df[i][2])/6\n",
    "    else:\n",
    "        pfloat = int(df[i])/1\n",
    "    DScoreBoy.loc[i,'男1000米跑']= round(pfloat,3)\n",
    "\n",
    "df = pd.Series(data=DScoreStd['男1000米跑','成绩'])\n",
    "\n",
    "for i in range(0,len(df.index)):\n",
    "    x= int(df[i][0])\n",
    "    pfloat = x +int(df[i][2:4])/60\n",
    "    DScoreStd.loc[i,('男1000米跑','成绩')]= round(pfloat,3)\n",
    "\n",
    "df = pd.Series(data=DScoreStd['女800米跑','成绩'])\n",
    "\n",
    "for i in range(0,len(df.index)):\n",
    "    x= int(df[i][0])\n",
    "    pfloat = x +int(df[i][2:4])/60\n",
    "    DScoreStd.loc[i,('女800米跑','成绩')]= round(pfloat,3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3284b290",
   "metadata": {},
   "source": [
    "所有数值统一为float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "1b6a04ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.217</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.267</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.150</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.350</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.733</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.383</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.317</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.417</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.650</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高    体重  BMI\n",
       "0     1  男    4.217   8.88  195.0  12.0   1.0  2785.0  170.0  72.6  0.0\n",
       "1     1  男    4.267   7.70  225.0  11.0   7.0  3133.0  174.0  52.7  0.0\n",
       "2     1  男    4.150   8.45  218.0  14.0   1.0  3901.0  169.0  46.5  0.0\n",
       "3     1  男    4.350   8.05  206.0  13.0   1.0  4946.0  183.0  79.7  0.0\n",
       "4     1  男    3.733   7.52  210.0  13.0   9.0  3538.0  171.0  54.7  0.0\n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...   ...  ...\n",
       "472  17  男    4.383   8.27  208.0  10.0   0.0  4647.0  176.0  69.5  0.0\n",
       "473  17  男    5.317   9.55  210.0  15.0   6.0  7042.0  177.0  76.0  0.0\n",
       "474  17  男    3.417   7.50  252.0  13.0  13.0  5755.0  181.0  65.0  0.0\n",
       "475  17  男    4.650   7.81  208.0  14.0  11.0  5688.0  172.0  51.7  0.0\n",
       "476  17  男    0.000   0.00    0.0   0.0   0.0     0.0    0.0   0.0  0.0\n",
       "\n",
       "[477 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI\n",
       "0     1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  0.0\n",
       "1     1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  0.0\n",
       "2     1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  0.0\n",
       "3     1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  0.0\n",
       "4     1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  0.0\n",
       "..   .. ..     ...    ...    ...   ...   ...     ...    ...   ...  ...\n",
       "588  17  女    3.51   9.60  150.0  24.0  41.0  2255.0  158.0  49.0  0.0\n",
       "589  17  女    4.00  10.18  150.0  13.0  36.0  2937.0  161.0  55.7  0.0\n",
       "590  17  女    3.45  10.18  152.0  15.0  35.0  2592.0  165.0  48.6  0.0\n",
       "591  17  女    4.01   9.67  165.0  10.0  41.0  1829.0  154.0  43.6  0.0\n",
       "592  17  女    4.48   9.09  180.0  10.0  46.0  2962.0  162.0  55.3  0.0\n",
       "\n",
       "[593 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 男生成绩转换为浮点\n",
    "dft1 = pd.DataFrame(DScoreBoy.iloc[:,0:2])\n",
    "dft2 = pd.DataFrame(DScoreBoy.iloc[:,2:].astype('float'))\n",
    "dfboy = pd.concat([dft1,dft2],axis=1)\n",
    "# 女生成绩转换为浮点\n",
    "dft1 = pd.DataFrame(DScoreGirl.iloc[:,0:2])\n",
    "dft2 = pd.DataFrame(DScoreGirl.iloc[:,2:].astype('float'))\n",
    "dfgirl = pd.concat([dft1,dft2],axis=1)\n",
    "\n",
    "display(dfboy,dfgirl)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29a3db74",
   "metadata": {},
   "source": [
    "生成分数标准区间表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "39c310b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">男肺活量</th>\n",
       "      <th colspan=\"4\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"4\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"4\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>成绩hi</th>\n",
       "      <th>成绩low</th>\n",
       "      <th>分数hi</th>\n",
       "      <th>分数low</th>\n",
       "      <th>成绩hi</th>\n",
       "      <th>成绩low</th>\n",
       "      <th>分数hi</th>\n",
       "      <th>分数low</th>\n",
       "      <th>成绩hi</th>\n",
       "      <th>成绩low</th>\n",
       "      <th>...</th>\n",
       "      <th>分数hi</th>\n",
       "      <th>分数low</th>\n",
       "      <th>成绩hi</th>\n",
       "      <th>成绩low</th>\n",
       "      <th>分数hi</th>\n",
       "      <th>分数low</th>\n",
       "      <th>成绩hi</th>\n",
       "      <th>成绩low</th>\n",
       "      <th>分数hi</th>\n",
       "      <th>分数low</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540.0</td>\n",
       "      <td>4420.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>3150.0</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>7.1</td>\n",
       "      <td>7.2</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>3.500</td>\n",
       "      <td>3.583</td>\n",
       "      <td>100.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>3.400</td>\n",
       "      <td>3.500</td>\n",
       "      <td>100.0</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420.0</td>\n",
       "      <td>4300.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>3050.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>7.3</td>\n",
       "      <td>...</td>\n",
       "      <td>95.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3.583</td>\n",
       "      <td>3.667</td>\n",
       "      <td>95.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3.500</td>\n",
       "      <td>3.600</td>\n",
       "      <td>95.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300.0</td>\n",
       "      <td>4050.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3050.0</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.4</td>\n",
       "      <td>...</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3.667</td>\n",
       "      <td>3.783</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3.600</td>\n",
       "      <td>3.717</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050.0</td>\n",
       "      <td>3800.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>2750.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.5</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3.783</td>\n",
       "      <td>3.917</td>\n",
       "      <td>85.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3.717</td>\n",
       "      <td>3.833</td>\n",
       "      <td>85.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800.0</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>2750.0</td>\n",
       "      <td>2650.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.7</td>\n",
       "      <td>...</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>3.917</td>\n",
       "      <td>4.000</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>3.833</td>\n",
       "      <td>3.917</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3680.0</td>\n",
       "      <td>3560.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>2650.0</td>\n",
       "      <td>2550.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>7.9</td>\n",
       "      <td>...</td>\n",
       "      <td>78.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.083</td>\n",
       "      <td>78.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3.917</td>\n",
       "      <td>4.000</td>\n",
       "      <td>78.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3560.0</td>\n",
       "      <td>3440.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>2550.0</td>\n",
       "      <td>2450.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>8.1</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>4.083</td>\n",
       "      <td>4.167</td>\n",
       "      <td>76.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.083</td>\n",
       "      <td>76.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3440.0</td>\n",
       "      <td>3320.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2450.0</td>\n",
       "      <td>2350.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>8.1</td>\n",
       "      <td>8.3</td>\n",
       "      <td>...</td>\n",
       "      <td>74.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>4.167</td>\n",
       "      <td>4.250</td>\n",
       "      <td>74.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>4.083</td>\n",
       "      <td>4.167</td>\n",
       "      <td>74.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3320.0</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>2350.0</td>\n",
       "      <td>2250.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>8.5</td>\n",
       "      <td>...</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>4.250</td>\n",
       "      <td>4.333</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>4.167</td>\n",
       "      <td>4.250</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3200.0</td>\n",
       "      <td>3080.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>2250.0</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>8.7</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>4.333</td>\n",
       "      <td>4.417</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>4.250</td>\n",
       "      <td>4.333</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3080.0</td>\n",
       "      <td>2960.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>2050.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.9</td>\n",
       "      <td>...</td>\n",
       "      <td>68.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>4.417</td>\n",
       "      <td>4.500</td>\n",
       "      <td>68.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>4.333</td>\n",
       "      <td>4.417</td>\n",
       "      <td>68.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2960.0</td>\n",
       "      <td>2840.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2050.0</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>8.9</td>\n",
       "      <td>9.1</td>\n",
       "      <td>...</td>\n",
       "      <td>66.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>4.500</td>\n",
       "      <td>4.583</td>\n",
       "      <td>66.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>4.417</td>\n",
       "      <td>4.500</td>\n",
       "      <td>66.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2840.0</td>\n",
       "      <td>2720.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>1850.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.3</td>\n",
       "      <td>...</td>\n",
       "      <td>64.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4.583</td>\n",
       "      <td>4.667</td>\n",
       "      <td>64.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4.500</td>\n",
       "      <td>4.583</td>\n",
       "      <td>64.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2720.0</td>\n",
       "      <td>2600.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1850.0</td>\n",
       "      <td>1750.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>9.3</td>\n",
       "      <td>9.5</td>\n",
       "      <td>...</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4.667</td>\n",
       "      <td>4.750</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4.583</td>\n",
       "      <td>4.667</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2600.0</td>\n",
       "      <td>2470.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1750.0</td>\n",
       "      <td>1710.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>9.5</td>\n",
       "      <td>9.7</td>\n",
       "      <td>...</td>\n",
       "      <td>60.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>4.750</td>\n",
       "      <td>5.083</td>\n",
       "      <td>60.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>4.667</td>\n",
       "      <td>4.833</td>\n",
       "      <td>60.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2470.0</td>\n",
       "      <td>2340.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1710.0</td>\n",
       "      <td>1670.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>9.7</td>\n",
       "      <td>9.9</td>\n",
       "      <td>...</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>5.083</td>\n",
       "      <td>5.417</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>4.833</td>\n",
       "      <td>5.000</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2340.0</td>\n",
       "      <td>2210.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1670.0</td>\n",
       "      <td>1630.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>9.9</td>\n",
       "      <td>10.1</td>\n",
       "      <td>...</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5.417</td>\n",
       "      <td>5.750</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5.000</td>\n",
       "      <td>5.167</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2210.0</td>\n",
       "      <td>2080.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1630.0</td>\n",
       "      <td>1590.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.1</td>\n",
       "      <td>10.3</td>\n",
       "      <td>...</td>\n",
       "      <td>30.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>5.750</td>\n",
       "      <td>6.083</td>\n",
       "      <td>30.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>5.167</td>\n",
       "      <td>5.333</td>\n",
       "      <td>30.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2080.0</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1590.0</td>\n",
       "      <td>1550.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.3</td>\n",
       "      <td>10.5</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6.083</td>\n",
       "      <td>6.417</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.333</td>\n",
       "      <td>5.500</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      男肺活量                         女肺活量                      男50米跑        ...  \\\n",
       "      成绩hi   成绩low   分数hi 分数low    成绩hi   成绩low   分数hi 分数low  成绩hi 成绩low  ...   \n",
       "0   4540.0  4420.0  100.0  95.0  3150.0  3100.0  100.0  95.0   7.1   7.2  ...   \n",
       "1   4420.0  4300.0   95.0  90.0  3100.0  3050.0   95.0  90.0   7.2   7.3  ...   \n",
       "2   4300.0  4050.0   90.0  85.0  3050.0  2900.0   90.0  85.0   7.3   7.4  ...   \n",
       "3   4050.0  3800.0   85.0  80.0  2900.0  2750.0   85.0  80.0   7.4   7.5  ...   \n",
       "4   3800.0  3680.0   80.0  78.0  2750.0  2650.0   80.0  78.0   7.5   7.7  ...   \n",
       "5   3680.0  3560.0   78.0  76.0  2650.0  2550.0   78.0  76.0   7.7   7.9  ...   \n",
       "6   3560.0  3440.0   76.0  74.0  2550.0  2450.0   76.0  74.0   7.9   8.1  ...   \n",
       "7   3440.0  3320.0   74.0  72.0  2450.0  2350.0   74.0  72.0   8.1   8.3  ...   \n",
       "8   3320.0  3200.0   72.0  70.0  2350.0  2250.0   72.0  70.0   8.3   8.5  ...   \n",
       "9   3200.0  3080.0   70.0  68.0  2250.0  2150.0   70.0  68.0   8.5   8.7  ...   \n",
       "10  3080.0  2960.0   68.0  66.0  2150.0  2050.0   68.0  66.0   8.7   8.9  ...   \n",
       "11  2960.0  2840.0   66.0  64.0  2050.0  1950.0   66.0  64.0   8.9   9.1  ...   \n",
       "12  2840.0  2720.0   64.0  62.0  1950.0  1850.0   64.0  62.0   9.1   9.3  ...   \n",
       "13  2720.0  2600.0   62.0  60.0  1850.0  1750.0   62.0  60.0   9.3   9.5  ...   \n",
       "14  2600.0  2470.0   60.0  50.0  1750.0  1710.0   60.0  50.0   9.5   9.7  ...   \n",
       "15  2470.0  2340.0   50.0  40.0  1710.0  1670.0   50.0  40.0   9.7   9.9  ...   \n",
       "16  2340.0  2210.0   40.0  30.0  1670.0  1630.0   40.0  30.0   9.9  10.1  ...   \n",
       "17  2210.0  2080.0   30.0  20.0  1630.0  1590.0   30.0  20.0  10.1  10.3  ...   \n",
       "18  2080.0  1950.0   20.0  10.0  1590.0  1550.0   20.0  10.0  10.3  10.5  ...   \n",
       "\n",
       "      女仰卧       男1000米跑                     女800米跑                      \n",
       "     分数hi 分数low    成绩hi  成绩low   分数hi 分数low   成绩hi  成绩low   分数hi 分数low  \n",
       "0   100.0  95.0   3.500  3.583  100.0  95.0  3.400  3.500  100.0  95.0  \n",
       "1    95.0  90.0   3.583  3.667   95.0  90.0  3.500  3.600   95.0  90.0  \n",
       "2    90.0  85.0   3.667  3.783   90.0  85.0  3.600  3.717   90.0  85.0  \n",
       "3    85.0  80.0   3.783  3.917   85.0  80.0  3.717  3.833   85.0  80.0  \n",
       "4    80.0  78.0   3.917  4.000   80.0  78.0  3.833  3.917   80.0  78.0  \n",
       "5    78.0  76.0   4.000  4.083   78.0  76.0  3.917  4.000   78.0  76.0  \n",
       "6    76.0  74.0   4.083  4.167   76.0  74.0  4.000  4.083   76.0  74.0  \n",
       "7    74.0  72.0   4.167  4.250   74.0  72.0  4.083  4.167   74.0  72.0  \n",
       "8    72.0  70.0   4.250  4.333   72.0  70.0  4.167  4.250   72.0  70.0  \n",
       "9    70.0  68.0   4.333  4.417   70.0  68.0  4.250  4.333   70.0  68.0  \n",
       "10   68.0  66.0   4.417  4.500   68.0  66.0  4.333  4.417   68.0  66.0  \n",
       "11   66.0  64.0   4.500  4.583   66.0  64.0  4.417  4.500   66.0  64.0  \n",
       "12   64.0  62.0   4.583  4.667   64.0  62.0  4.500  4.583   64.0  62.0  \n",
       "13   62.0  60.0   4.667  4.750   62.0  60.0  4.583  4.667   62.0  60.0  \n",
       "14   60.0  50.0   4.750  5.083   60.0  50.0  4.667  4.833   60.0  50.0  \n",
       "15   50.0  40.0   5.083  5.417   50.0  40.0  4.833  5.000   50.0  40.0  \n",
       "16   40.0  30.0   5.417  5.750   40.0  30.0  5.000  5.167   40.0  30.0  \n",
       "17   30.0  20.0   5.750  6.083   30.0  20.0  5.167  5.333   30.0  20.0  \n",
       "18   20.0  10.0   6.083  6.417   20.0  10.0  5.333  5.500   20.0  10.0  \n",
       "\n",
       "[19 rows x 48 columns]"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftemp = pd.Series(data= ['男肺活量','女肺活量','男50米跑','女50米跑',\n",
    "                          '男体前屈','女体前屈','男跳远','女跳远',\n",
    "                          '男引体','女仰卧','男1000米跑','女800米跑']\n",
    "                  )\n",
    "dfscore =pd.DataFrame(columns= pd.MultiIndex.from_product([['男肺活量','女肺活量','男50米跑','女50米跑',\n",
    "                                                            '男体前屈','女体前屈','男跳远','女跳远',\n",
    "                                                            '男引体','女仰卧','男1000米跑','女800米跑'],\n",
    "                                                           ['成绩hi','成绩low','分数hi','分数low']]),\n",
    "                     index= range(0,len(DScoreStd.index)-1))\n",
    "for i in range(0,len(DScoreStd.index)-1):\n",
    "    for j in range(0,len(dftemp)):\n",
    "        dfscore.loc[i,(dftemp[j],'成绩hi')] = DScoreStd.loc[i,(dftemp[j],'成绩')]\n",
    "        if pd.isna(DScoreStd.loc[i,(dftemp[j],'分数')] ) == True:\n",
    "            dfscore.loc[i,(dftemp[j],'分数hi')] = (DScoreStd.loc[i+1,(dftemp[j],'分数')]+DScoreStd.loc[i-1,(dftemp[j],'分数')])/2\n",
    "        else:\n",
    "            dfscore.loc[i,(dftemp[j],'分数hi')] = DScoreStd.loc[i,(dftemp[j],'分数')]\n",
    "        \n",
    "        dfscore.loc[i,(dftemp[j],'成绩low')] = DScoreStd.loc[i+1,(dftemp[j],'成绩')]\n",
    "        if pd.isna(DScoreStd.loc[i+1,(dftemp[j],'分数')] ) == True:\n",
    "            dfscore.loc[i,(dftemp[j],'分数low')] = (DScoreStd.loc[i+2,(dftemp[j],'分数')]+DScoreStd.loc[i,(dftemp[j],'分数')])/2\n",
    "        else:\n",
    "            dfscore.loc[i,(dftemp[j],'分数low')] = DScoreStd.loc[i+1,(dftemp[j],'分数')]\n",
    "        \n",
    "dfscore = pd.DataFrame(data=dfscore.astype('float'))\n",
    "dfscore"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6789d94",
   "metadata": {},
   "source": [
    "## 要求5、对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "326ec178",
   "metadata": {},
   "outputs": [],
   "source": [
    "def FindScore(s_hi,s_low,r_hi,r_low,r_now):\n",
    "    s_now = (((r_now-r_low)*(s_hi-s_low))/(r_hi-r_low))+s_low\n",
    "    return round(s_now,2)\n",
    "# 一次函数拟合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "60e8c0cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def StrConcat(s1,s2):\n",
    "    s=s1+s2\n",
    "    return s\n",
    "# 字符串合并"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40e71239",
   "metadata": {},
   "source": [
    "男生成绩转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "944918af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.217</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>63.08</td>\n",
       "      <td>66.2</td>\n",
       "      <td>75.71</td>\n",
       "      <td>60.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.267</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68.88</td>\n",
       "      <td>78.0</td>\n",
       "      <td>74.29</td>\n",
       "      <td>75.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>71.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.150</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>82.02</td>\n",
       "      <td>70.5</td>\n",
       "      <td>78.57</td>\n",
       "      <td>71.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>74.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.350</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>74.5</td>\n",
       "      <td>77.14</td>\n",
       "      <td>65.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>69.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.733</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>75.63</td>\n",
       "      <td>79.8</td>\n",
       "      <td>77.14</td>\n",
       "      <td>67.5</td>\n",
       "      <td>68.0</td>\n",
       "      <td>87.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.383</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>72.3</td>\n",
       "      <td>72.86</td>\n",
       "      <td>66.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.317</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>57.5</td>\n",
       "      <td>80.00</td>\n",
       "      <td>67.5</td>\n",
       "      <td>50.0</td>\n",
       "      <td>42.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.417</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>80.0</td>\n",
       "      <td>77.14</td>\n",
       "      <td>92.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.650</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>76.9</td>\n",
       "      <td>78.57</td>\n",
       "      <td>66.5</td>\n",
       "      <td>76.0</td>\n",
       "      <td>62.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高    体重  BMI  \\\n",
       "0     1  男    4.217   8.88  195.0  12.0   1.0  2785.0  170.0  72.6  0.0   \n",
       "1     1  男    4.267   7.70  225.0  11.0   7.0  3133.0  174.0  52.7  0.0   \n",
       "2     1  男    4.150   8.45  218.0  14.0   1.0  3901.0  169.0  46.5  0.0   \n",
       "3     1  男    4.350   8.05  206.0  13.0   1.0  4946.0  183.0  79.7  0.0   \n",
       "4     1  男    3.733   7.52  210.0  13.0   9.0  3538.0  171.0  54.7  0.0   \n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...   ...  ...   \n",
       "472  17  男    4.383   8.27  208.0  10.0   0.0  4647.0  176.0  69.5  0.0   \n",
       "473  17  男    5.317   9.55  210.0  15.0   6.0  7042.0  177.0  76.0  0.0   \n",
       "474  17  男    3.417   7.50  252.0  13.0  13.0  5755.0  181.0  65.0  0.0   \n",
       "475  17  男    4.650   7.81  208.0  14.0  11.0  5688.0  172.0  51.7  0.0   \n",
       "476  17  男    0.000   0.00    0.0   0.0   0.0     0.0    0.0   0.0  0.0   \n",
       "\n",
       "     男肺活量分数  男50米跑分数  男体前屈分数  男跳远分数  男引体分数  男1000米跑分数  \n",
       "0     63.08     66.2   75.71   60.0   10.0      72.80  \n",
       "1     68.88     78.0   74.29   75.0   60.0      71.59  \n",
       "2     82.02     70.5   78.57   71.5   10.0      74.40  \n",
       "3    100.00     74.5   77.14   65.5   10.0      69.60  \n",
       "4     75.63     79.8   77.14   67.5   68.0      87.16  \n",
       "..      ...      ...     ...    ...    ...        ...  \n",
       "472  100.00     72.3   72.86   66.5   10.0      68.81  \n",
       "473  100.00     57.5   80.00   67.5   50.0      42.99  \n",
       "474  100.00     80.0   77.14   92.0   85.0     100.00  \n",
       "475  100.00     76.9   78.57   66.5   76.0      62.40  \n",
       "476   10.00    100.0   50.00   10.0   10.0     100.00  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftemp = pd.Series(data= ['男肺活量','男50米跑','男体前屈','男跳远','男引体','男1000米跑'] )\n",
    "\n",
    "for i in range(0,len(dftemp)):\n",
    "    for j in range(0,len(dfboy.index)):\n",
    "        for k in range(0,len(dfscore.index)):\n",
    "            if (dftemp[i] == '男50米跑') or (dftemp[i] == '男1000米跑'):\n",
    "                \n",
    "                if dfboy.loc[j,dftemp[i]] <= dfscore.loc[k,(dftemp[i],'成绩hi')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)\n",
    "                    break\n",
    "                elif dfboy.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)\n",
    "                    break\n",
    "                elif dfboy.loc[j,dftemp[i]] >= max(dfscore[dftemp[i],'成绩low']): #大于最低分成绩算最低分\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])\n",
    "                    break\n",
    "                elif dfscore.loc[k,(dftemp[i],'成绩hi')] < dfboy.loc[j,dftemp[i]] < dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],\n",
    "                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],\n",
    "                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],\n",
    "                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],\n",
    "                                                                       r_now=dfboy.loc[j,dftemp[i]]\n",
    "                                                                      )\n",
    "                    break\n",
    "                \n",
    "            elif dftemp[i] == '男引体':  #特殊处理\n",
    "                if pd.isna(dfscore.loc[k,(dftemp[i],'成绩hi')]):\n",
    "                    continue\n",
    "                elif dfboy.loc[j,dftemp[i]] <= min(dfscore[dftemp[i],'成绩low']): #小于最低分成绩算最低分\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])\n",
    "                    break\n",
    "                elif dfboy.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩hi')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)\n",
    "                    break\n",
    "                \n",
    "            else:\n",
    "                if dfboy.loc[j,dftemp[i]] >= dfscore.loc[k,(dftemp[i],'成绩hi')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)\n",
    "                    break\n",
    "                elif dfboy.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)\n",
    "                    break\n",
    "                elif dfboy.loc[j,dftemp[i]] <= min(dfscore[dftemp[i],'成绩low']): #小于最低分成绩算最低分\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])\n",
    "                    break\n",
    "                elif dfscore.loc[k,(dftemp[i],'成绩hi')] > dfboy.loc[j,dftemp[i]] > dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],\n",
    "                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],\n",
    "                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],\n",
    "                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],\n",
    "                                                                       r_now=dfboy.loc[j,dftemp[i]]\n",
    "                                                                      )\n",
    "                    break\n",
    "dfboy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b18f800",
   "metadata": {},
   "source": [
    "女生成绩转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "095afc5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女800米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>72.8</td>\n",
       "      <td>77.85</td>\n",
       "      <td>85.00</td>\n",
       "      <td>88.33</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>18.0</td>\n",
       "      <td>67.08</td>\n",
       "      <td>60.00</td>\n",
       "      <td>66.00</td>\n",
       "      <td>61.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>10.0</td>\n",
       "      <td>64.00</td>\n",
       "      <td>61.33</td>\n",
       "      <td>77.00</td>\n",
       "      <td>97.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>70.8</td>\n",
       "      <td>90.59</td>\n",
       "      <td>76.00</td>\n",
       "      <td>85.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>68.1</td>\n",
       "      <td>65.54</td>\n",
       "      <td>54.00</td>\n",
       "      <td>71.00</td>\n",
       "      <td>98.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.10</td>\n",
       "      <td>70.0</td>\n",
       "      <td>99.41</td>\n",
       "      <td>61.33</td>\n",
       "      <td>78.00</td>\n",
       "      <td>94.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.23</td>\n",
       "      <td>64.2</td>\n",
       "      <td>73.23</td>\n",
       "      <td>61.33</td>\n",
       "      <td>73.00</td>\n",
       "      <td>76.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>76.84</td>\n",
       "      <td>64.2</td>\n",
       "      <td>76.31</td>\n",
       "      <td>62.67</td>\n",
       "      <td>72.00</td>\n",
       "      <td>97.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>61.58</td>\n",
       "      <td>69.3</td>\n",
       "      <td>68.62</td>\n",
       "      <td>71.33</td>\n",
       "      <td>78.00</td>\n",
       "      <td>75.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>87.07</td>\n",
       "      <td>75.1</td>\n",
       "      <td>68.62</td>\n",
       "      <td>81.43</td>\n",
       "      <td>85.00</td>\n",
       "      <td>64.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI  \\\n",
       "0     1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  0.0   \n",
       "1     1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  0.0   \n",
       "2     1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  0.0   \n",
       "3     1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  0.0   \n",
       "4     1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  0.0   \n",
       "..   .. ..     ...    ...    ...   ...   ...     ...    ...   ...  ...   \n",
       "588  17  女    3.51   9.60  150.0  24.0  41.0  2255.0  158.0  49.0  0.0   \n",
       "589  17  女    4.00  10.18  150.0  13.0  36.0  2937.0  161.0  55.7  0.0   \n",
       "590  17  女    3.45  10.18  152.0  15.0  35.0  2592.0  165.0  48.6  0.0   \n",
       "591  17  女    4.01   9.67  165.0  10.0  41.0  1829.0  154.0  43.6  0.0   \n",
       "592  17  女    4.48   9.09  180.0  10.0  46.0  2962.0  162.0  55.3  0.0   \n",
       "\n",
       "     女肺活量分数  女50米跑分数  女体前屈分数  女跳远分数  女仰卧分数  女800米跑分数  \n",
       "0    100.00     72.8   77.85  85.00  88.33    100.00  \n",
       "1    100.00     18.0   67.08  60.00  66.00     61.83  \n",
       "2    100.00     10.0   64.00  61.33  77.00     97.00  \n",
       "3    100.00     70.8   90.59  76.00  85.00    100.00  \n",
       "4    100.00     68.1   65.54  54.00  71.00     98.50  \n",
       "..      ...      ...     ...    ...    ...       ...  \n",
       "588   70.10     70.0   99.41  61.33  78.00     94.50  \n",
       "589   86.23     64.2   73.23  61.33  73.00     76.00  \n",
       "590   76.84     64.2   76.31  62.67  72.00     97.50  \n",
       "591   61.58     69.3   68.62  71.33  78.00     75.76  \n",
       "592   87.07     75.1   68.62  81.43  85.00     64.48  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftemp = pd.Series(data= ['女肺活量','女50米跑','女体前屈','女跳远','女仰卧','女800米跑'] )\n",
    "\n",
    "for i in range(0,len(dftemp)):\n",
    "    for j in range(0,len(dfgirl.index)):\n",
    "        for k in range(0,len(dfscore.index)):\n",
    "            if (dftemp[i] == '女50米跑') or (dftemp[i] == '女800米跑'):\n",
    "                if dfgirl.loc[j,dftemp[i]] <= dfscore.loc[k,(dftemp[i],'成绩hi')]:\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)\n",
    "                    break\n",
    "                elif dfgirl.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)\n",
    "                    break\n",
    "                elif dfgirl.loc[j,dftemp[i]] >= max(dfscore[dftemp[i],'成绩low']): #大于最低分成绩算最低分\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])\n",
    "                    break\n",
    "                elif dfscore.loc[k,(dftemp[i],'成绩hi')] < dfgirl.loc[j,dftemp[i]] < dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],\n",
    "                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],\n",
    "                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],\n",
    "                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],\n",
    "                                                                       r_now=dfgirl.loc[j,dftemp[i]]\n",
    "                                                                      )\n",
    "                    break\n",
    "                \n",
    "                          \n",
    "            else:\n",
    "                if dfgirl.loc[j,dftemp[i]] >= dfscore.loc[k,(dftemp[i],'成绩hi')]:\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)\n",
    "                    break\n",
    "                elif dfgirl.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)\n",
    "                    break\n",
    "                elif dfgirl.loc[j,dftemp[i]] <= min(dfscore[dftemp[i],'成绩low']): #小于最低分成绩算最低分\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])\n",
    "                    break\n",
    "                elif dfscore.loc[k,(dftemp[i],'成绩hi')] > dfgirl.loc[j,dftemp[i]] > dfscore.loc[k,(dftemp[i],'成绩low')]:\n",
    "                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],\n",
    "                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],\n",
    "                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],\n",
    "                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],\n",
    "                                                                       r_now=dfgirl.loc[j,dftemp[i]] \n",
    "                                                                      )\n",
    "                    break\n",
    "dfgirl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8498e124",
   "metadata": {},
   "source": [
    "## 最终结果输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "3893bf2f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0,  1,  2, 16,  3, 12,  4, 14,  5, 13,  6, 15,  7, 11,  8,  9, 10])"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = [0,1,2,16,3,12,4,14,5,13,6,15,7,11,8,9,10]\n",
    "newcol = np.array(a)\n",
    "newcol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "c6e43ddd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.217</td>\n",
       "      <td>72.80</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66.2</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>75.71</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>63.08</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.267</td>\n",
       "      <td>71.59</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74.29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>68.88</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.150</td>\n",
       "      <td>74.40</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70.5</td>\n",
       "      <td>218.0</td>\n",
       "      <td>71.5</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.57</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>82.02</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.350</td>\n",
       "      <td>69.60</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74.5</td>\n",
       "      <td>206.0</td>\n",
       "      <td>65.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>77.14</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.733</td>\n",
       "      <td>87.16</td>\n",
       "      <td>7.52</td>\n",
       "      <td>79.8</td>\n",
       "      <td>210.0</td>\n",
       "      <td>67.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>77.14</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>75.63</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.383</td>\n",
       "      <td>68.81</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72.3</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.317</td>\n",
       "      <td>42.99</td>\n",
       "      <td>9.55</td>\n",
       "      <td>57.5</td>\n",
       "      <td>210.0</td>\n",
       "      <td>67.5</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80.00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.417</td>\n",
       "      <td>100.00</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>77.14</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.650</td>\n",
       "      <td>62.40</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76.9</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.57</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.000</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男1000米跑分数  男50米跑  男50米跑分数    男跳远  男跳远分数  男体前屈  男体前屈分数  \\\n",
       "0     1  男    4.217      72.80   8.88     66.2  195.0   60.0  12.0   75.71   \n",
       "1     1  男    4.267      71.59   7.70     78.0  225.0   75.0  11.0   74.29   \n",
       "2     1  男    4.150      74.40   8.45     70.5  218.0   71.5  14.0   78.57   \n",
       "3     1  男    4.350      69.60   8.05     74.5  206.0   65.5  13.0   77.14   \n",
       "4     1  男    3.733      87.16   7.52     79.8  210.0   67.5  13.0   77.14   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男    4.383      68.81   8.27     72.3  208.0   66.5  10.0   72.86   \n",
       "473  17  男    5.317      42.99   9.55     57.5  210.0   67.5  15.0   80.00   \n",
       "474  17  男    3.417     100.00   7.50     80.0  252.0   92.0  13.0   77.14   \n",
       "475  17  男    4.650      62.40   7.81     76.9  208.0   66.5  14.0   78.57   \n",
       "476  17  男    0.000     100.00   0.00    100.0    0.0   10.0   0.0   50.00   \n",
       "\n",
       "      男引体  男引体分数    男肺活量  男肺活量分数     身高    体重  BMI  \n",
       "0     1.0   10.0  2785.0   63.08  170.0  72.6  0.0  \n",
       "1     7.0   60.0  3133.0   68.88  174.0  52.7  0.0  \n",
       "2     1.0   10.0  3901.0   82.02  169.0  46.5  0.0  \n",
       "3     1.0   10.0  4946.0  100.00  183.0  79.7  0.0  \n",
       "4     9.0   68.0  3538.0   75.63  171.0  54.7  0.0  \n",
       "..    ...    ...     ...     ...    ...   ...  ...  \n",
       "472   0.0   10.0  4647.0  100.00  176.0  69.5  0.0  \n",
       "473   6.0   50.0  7042.0  100.00  177.0  76.0  0.0  \n",
       "474  13.0   85.0  5755.0  100.00  181.0  65.0  0.0  \n",
       "475  11.0   76.0  5688.0  100.00  172.0  51.7  0.0  \n",
       "476   0.0   10.0     0.0   10.00    0.0   0.0  0.0  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100.00</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72.8</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85.00</td>\n",
       "      <td>16.0</td>\n",
       "      <td>77.85</td>\n",
       "      <td>48.0</td>\n",
       "      <td>88.33</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>61.83</td>\n",
       "      <td>11.44</td>\n",
       "      <td>18.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60.00</td>\n",
       "      <td>9.0</td>\n",
       "      <td>67.08</td>\n",
       "      <td>29.0</td>\n",
       "      <td>66.00</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>97.00</td>\n",
       "      <td>13.40</td>\n",
       "      <td>10.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>61.33</td>\n",
       "      <td>7.0</td>\n",
       "      <td>64.00</td>\n",
       "      <td>40.0</td>\n",
       "      <td>77.00</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>100.00</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70.8</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76.00</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90.59</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85.00</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>98.50</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68.1</td>\n",
       "      <td>145.0</td>\n",
       "      <td>54.00</td>\n",
       "      <td>8.0</td>\n",
       "      <td>65.54</td>\n",
       "      <td>34.0</td>\n",
       "      <td>71.00</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>94.50</td>\n",
       "      <td>9.60</td>\n",
       "      <td>70.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>61.33</td>\n",
       "      <td>24.0</td>\n",
       "      <td>99.41</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78.00</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>70.10</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>76.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64.2</td>\n",
       "      <td>150.0</td>\n",
       "      <td>61.33</td>\n",
       "      <td>13.0</td>\n",
       "      <td>73.23</td>\n",
       "      <td>36.0</td>\n",
       "      <td>73.00</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>86.23</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>97.50</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64.2</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62.67</td>\n",
       "      <td>15.0</td>\n",
       "      <td>76.31</td>\n",
       "      <td>35.0</td>\n",
       "      <td>72.00</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>76.84</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>75.76</td>\n",
       "      <td>9.67</td>\n",
       "      <td>69.3</td>\n",
       "      <td>165.0</td>\n",
       "      <td>71.33</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68.62</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78.00</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>61.58</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>64.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>75.1</td>\n",
       "      <td>180.0</td>\n",
       "      <td>81.43</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68.62</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85.00</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>87.07</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女800米跑分数  女50米跑  女50米跑分数    女跳远  女跳远分数  女体前屈  女体前屈分数  \\\n",
       "0     1  女    3.22    100.00   9.32     72.8  185.0  85.00  16.0   77.85   \n",
       "1     1  女    4.59     61.83  11.44     18.0  148.0  60.00   9.0   67.08   \n",
       "2     1  女    3.46     97.00  13.40     10.0  150.0  61.33   7.0   64.00   \n",
       "3     1  女    3.39    100.00   9.52     70.8  172.0  76.00  21.0   90.59   \n",
       "4     1  女    3.43     98.50   9.79     68.1  145.0  54.00   8.0   65.54   \n",
       "..   .. ..     ...       ...    ...      ...    ...    ...   ...     ...   \n",
       "588  17  女    3.51     94.50   9.60     70.0  150.0  61.33  24.0   99.41   \n",
       "589  17  女    4.00     76.00  10.18     64.2  150.0  61.33  13.0   73.23   \n",
       "590  17  女    3.45     97.50  10.18     64.2  152.0  62.67  15.0   76.31   \n",
       "591  17  女    4.01     75.76   9.67     69.3  165.0  71.33  10.0   68.62   \n",
       "592  17  女    4.48     64.48   9.09     75.1  180.0  81.43  10.0   68.62   \n",
       "\n",
       "      女仰卧  女仰卧分数    女肺活量  女肺活量分数     身高    体重  BMI  \n",
       "0    48.0  88.33  3775.0  100.00  163.0  51.3  0.0  \n",
       "1    29.0  66.00  3683.0  100.00  163.0  66.6  0.0  \n",
       "2    40.0  77.00  3331.0  100.00  157.0  60.0  0.0  \n",
       "3    46.0  85.00  3701.0  100.00  160.0  50.7  0.0  \n",
       "4    34.0  71.00  3592.0  100.00  167.0  63.9  0.0  \n",
       "..    ...    ...     ...     ...    ...   ...  ...  \n",
       "588  41.0  78.00  2255.0   70.10  158.0  49.0  0.0  \n",
       "589  36.0  73.00  2937.0   86.23  161.0  55.7  0.0  \n",
       "590  35.0  72.00  2592.0   76.84  165.0  48.6  0.0  \n",
       "591  41.0  78.00  1829.0   61.58  154.0  43.6  0.0  \n",
       "592  46.0  85.00  2962.0   87.07  162.0  55.3  0.0  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dfboy= dfboy.take(newcol,axis= 1)\n",
    "dfgirl= dfgirl.take(newcol,axis= 1)\n",
    "display(dfboy,dfgirl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91afcfe4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62b158bb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "303.825px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
